#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
@file add_yzh_grades.py
@description 为班级ID为6、课程ID为4(由教师ID 17教授)的35名学生添加成绩数据
@created 2024-05-23
"""

import sqlite3
import random
from datetime import datetime

# 数据库连接
conn = sqlite3.connect('instance/ai_teach.db')
cursor = conn.cursor()

# 1. 获取教学安排ID (teaching_assignment_id)
cursor.execute('''
    SELECT id FROM yzh_teaching_assignments 
    WHERE teacher_id = 17 AND course_id = 4 AND class_id = 6 AND status = 1
''')
result = cursor.fetchone()

if result:
    assignment_id = result[0]
    print(f"找到教学安排ID: {assignment_id}")
else:
    # 如果没有找到对应的教学安排，则创建一个
    print("未找到对应的教学安排，正在创建...")
    cursor.execute('''
        INSERT INTO yzh_teaching_assignments (teacher_id, course_id, class_id, status, created_at) 
        VALUES (17, 4, 6, 1, ?)
    ''', (datetime.now().strftime('%Y-%m-%d %H:%M:%S'),))
    assignment_id = cursor.lastrowid
    print(f"已创建教学安排ID: {assignment_id}")

# 2. 获取班级6中的所有学生ID
cursor.execute('''
    SELECT id FROM yzh_students 
    WHERE class_id = 6
''')
students = cursor.fetchall()
print(f"班级6中有 {len(students)} 名学生")

# 3. 为每个学生添加成绩
for student in students:
    student_id = student[0]
    
    # 检查是否已存在该学生的成绩记录
    cursor.execute('''
        SELECT id FROM yzh_grades 
        WHERE student_id = ? AND teaching_assignment_id = ?
    ''', (student_id, assignment_id))
    
    if cursor.fetchone():
        print(f"学生ID {student_id} 的成绩记录已存在，跳过")
        continue
    
    # 生成随机成绩
    usual_score = round(random.uniform(60, 95), 1)  # 平时成绩
    exam_score = round(random.uniform(60, 95), 1)   # 考试成绩
    
    # 总评成绩 = 平时成绩*0.4 + 考试成绩*0.6
    final_score = round(usual_score * 0.4 + exam_score * 0.6, 1)
    
    # 根据总评成绩确定等级
    if final_score >= 90:
        grade_level = 'A'
    elif final_score >= 80:
        grade_level = 'B'
    elif final_score >= 70:
        grade_level = 'C'
    elif final_score >= 60:
        grade_level = 'D'
    else:
        grade_level = 'F'
    
    # 插入成绩记录
    cursor.execute('''
        INSERT INTO yzh_grades (
            student_id, teaching_assignment_id, course_id, 
            usual_score, exam_score, final_score, grade_level, 
            created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', (
        student_id, assignment_id, 4,
        usual_score, exam_score, final_score, grade_level,
        datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
        datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    ))
    
    print(f"已为学生ID {student_id} 添加成绩: 平时分={usual_score}, 考试分={exam_score}, 总评={final_score}, 等级={grade_level}")

# 提交事务并关闭连接
conn.commit()
print("所有成绩数据已添加完成")
conn.close() 